﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data.SqlClient;
using System.Data;
using System.ServiceModel;

namespace Dblayer
{
    public class DbMovie
    {
        private SqlCommand cmd = null;

        public DbMovie()
        {
        }

        public void createMovie(Movie movie)
        {
            try
            {
            Connection.dbconn.Open();
            string insert = @"insert into movie(Title, movielength) values ('" + movie.Title + "'," + "'" + movie.MovieLength + "')";
            cmd = Connection.GetDBCommand(insert);
            cmd.ExecuteReader();
            }
            catch(Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if(Connection.dbconn != null)
                {
                Connection.Close();
                }
            }
        }

        public Movie findMovie(string title)
        {
            string sql = "SELECT * from Movie WHERE Title = '" + title + "'";
            return BuildMovie(sql);
        }

        public List<Movie> findAllMovies()
        {
            string sql = "SELECT * from Movie";
            return BuildAllMovies(sql);
        }

        private List<Movie> BuildAllMovies(string sql)
        {
            IDataReader dbReader;
            List<Movie> movies = new List<Movie>();

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    Movie movie = new Movie((string)dbReader["Title"], (string)dbReader["MovieLength"]);
                    movies.Add(movie);
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find movie. Fejl: " + e.Message);
                movies = null;
                Connection.Close();
            }
            return movies;
        }

        private Movie BuildMovie(string sql)
        {
            IDataReader dbReader;
            Movie movie = null;

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    movie = new Movie((string)dbReader["Title"], (string)dbReader["MovieLength"]);
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find movie. Fejl: " + e.Message);
                movie = null;
                Connection.Close();
            }
            return movie;
        }

        public void updateMovie(Movie movie)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"update Movie set movielength = '" + movie.MovieLength + "', moviedescription = '" + movie.Description + "' WHERE title = '" + movie.Title + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error while updating Movie:\n\n " + e.Message + "\n\n" + e.StackTrace );
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public void deleteMovie(string title)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = "delete from movie where title = '" + title + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
                finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }
    }
}